﻿using Dapper;
using MySql.Data.MySqlClient;
using System.Collections.Generic;
using System.Linq;
using Net1.My.Web.Models;
using Net1.My.Web.Models.DB;
namespace Net1.My.Web.DAL
{
    public class DAL_students
    {
        private MySqlConnection mySqlConnection = new MySqlConnection(ConfigHelper.Configuration["MySQLConnectionString1"]);
        /// <summary>
        /// 返回对应id的一条学生数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public students single(int id)
        {
            var single = mySqlConnection.QueryFirstOrDefault<students>("select  * from students where id=@id", new { id });
            return single;
        }
        /// <summary>
        /// 获取数据
        /// </summary>
        /// <returns></returns>
        public List<students> getStudents()
        {
            var list = mySqlConnection.Query<students>("select  * from students ").ToList();
            return list;
        }
        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        public int insertStudents(students s)
        {
            var sql = "insert into students (student_name,class_id) values (@student_name,@class_id)";
            var affectedRows = mySqlConnection.Execute(sql, s);
            return affectedRows;
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        public int editStudents(students s)
        {
            var sql = "update students set student_name=@student_name,class_id=@class_id where id = @id;";
            var ar = mySqlConnection.Execute(sql, s);
            return ar;
        }
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int delStudents(int id)
        {
            var sql = "delete from students where id = @id;";
            var affectedRows = mySqlConnection.Execute(sql, new { id });
            return affectedRows;
        }
        public List<students> getso(string likestr)
        {
            var list = mySqlConnection.Query<students>($"select * from students where student_name like '%{likestr}%'").ToList();
            return list;
        }
    }
}
